﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_CRD_REPORT_Crd3014p_Get_Amount_By_Specific_Date')
	BEGIN
		DROP Procedure usp_UPDMS_CRD_REPORT_Crd3014p_Get_Amount_By_Specific_Date
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_CRD_REPORT_Crd3014p_Get_Amount_By_Specific_Date
**	Desc : 연간사용분석표에서 금액 누르면 상세 항목을 보여준다
**	Test Exec Query : Exec usp_UPDMS_CRD_REPORT_Crd3014p_Get_Amount_By_Specific_Date 'CC07X,CC06X','2011-03-11', 'ko'
**	Called by : Crd_Dac_UPDMS_CRD_REPORT_Crd3014p.cs
**	Program ID : Crd3014p
**	Auth : 송시명
**	Date : 2011-03-11
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_CRD_REPORT_Crd3014p_Get_Amount_By_Specific_Date]
@ls_card_cd nvarchar(max),
@ls_base_dt nvarchar(10),
@ls_lang_set nvarchar(2)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

--전월날짜 검색기간
SELECT 'P' AS Div,
       dbo.ufn_UPDMS_Get_Date_Add('D', 1, MIN(ucpn.Mass_Ed_Dt)) AS Date_From,
       dbo.ufn_UPDMS_Get_Date_Add('M', -1, @ls_base_dt) AS Date_To
  FROM UPDMS_CRD_PAYMNT_DT AS ucpn WITH(NOLOCK)
 WHERE Card_Cd IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@ls_card_cd))
   AND LEFT(ucpn.Paymnt_Dt, 7) = LEFT(dbo.ufn_UPDMS_Get_Date_Add('M', -1, @ls_base_dt), 7)
UNION ALL
--당월날짜 검색기간
SELECT 'C',
       dbo.ufn_UPDMS_Get_Date_Add('D', 1, MIN(ucpn.Mass_Ed_Dt)),
       @ls_base_dt
  FROM UPDMS_CRD_PAYMNT_DT AS ucpn WITH(NOLOCK)
 WHERE Card_Cd IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@ls_card_cd))
   AND LEFT(ucpn.Paymnt_Dt, 7) = LEFT(@ls_base_dt, 7);

 
SELECT CASE Pre_Account WHEN '' THEN Curr_Account 
                        ELSE Pre_Account
       END Account,
       Pre_Amount,
       Curr_Amount,
       Curr_Amount - Pre_Amount AS Diff_Amount
  FROM (
       SELECT dbo.ufn_UPDMS_Get_Code_Name(ISNULL(x.Pre_Account, 'AC999'), 'CRD', @ls_lang_set) AS Pre_Account,
              ISNULL(x.Pre_Amount, 0) AS Pre_Amount,
              dbo.ufn_UPDMS_Get_Code_Name(ISNULL(y.Curr_Account, 'AC999'), 'CRD', @ls_lang_set) AS Curr_Account,
              ISNULL(y.Curr_Amount, 0) AS Curr_Amount
         FROM (
              --전월날짜 합계금액
              SELECT Account AS Pre_Account,
                     ISNULL(SUM(Amount), 0) AS Pre_Amount
                FROM UPDMS_CRD_ITEM_BIZ AS a WITH(NOLOCK)
               WHERE Card_Cd IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@ls_card_cd))
                 AND a.Biz_Dt >= (SELECT dbo.ufn_UPDMS_Get_Date_Add('D', 1, MIN(ucpn.Mass_Ed_Dt))
                                    FROM UPDMS_CRD_PAYMNT_DT AS ucpn WITH(NOLOCK)
                                   WHERE LEFT(ucpn.Paymnt_Dt, 7) = LEFT(dbo.ufn_UPDMS_Get_Date_Add('M', -1, @ls_base_dt), 7))
                 AND a.Biz_Dt <= dbo.ufn_UPDMS_Get_Date_Add('M', -1, @ls_base_dt)
               GROUP BY Account
              ) x
         FULL OUTER JOIN (
              --당월날짜 합계금액
              SELECT Account AS Curr_Account,
                     ISNULL(SUM(Amount), 0) AS Curr_Amount
                FROM UPDMS_CRD_ITEM_BIZ AS a WITH(NOLOCK)
               WHERE Card_Cd IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@ls_card_cd))
                 AND a.Biz_Dt >= (SELECT dbo.ufn_UPDMS_Get_Date_Add('D', 1, MIN(ucpn.Mass_Ed_Dt))
                                    FROM UPDMS_CRD_PAYMNT_DT AS ucpn WITH(NOLOCK)
                                   WHERE LEFT(ucpn.Paymnt_Dt, 7) = LEFT(@ls_base_dt, 7))
                 AND a.Biz_Dt <= @ls_base_dt
               GROUP BY Account
              ) y
           ON x.Pre_Account = y.Curr_Account
       ) z

GO